﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Globalization;
using System.Linq;
using System.Text;
using Framework;

namespace Enterprise
{
    public class ArticleService : BaseService
    {

        /// <summary>
        /// 获取文章列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string title, int articleCategoryId, string language, int pageValue, int rowsValue, string sort = null, string order = null)
        {

            if (sort.IsNullOrEmpty())
            {
                sort = "CoDate";
            }
            if (order.IsNullOrEmpty())
            {
                order = "DESC";
            }
            var parOne = new[]
                             {
                                 new SQLiteParameter("@CoTitle", DbType.String, 255),
                                 new SQLiteParameter("@CoArticleCategoryId", DbType.Int32),
                                 new SQLiteParameter("@CoLanguage", DbType.String, 255)
                             };
            parOne[0].Value = title.SqliteLike();
            parOne[1].Value = articleCategoryId;
            parOne[2].Value = language.SqliteLike();
            var strWhere = new StringBuilder();
            strWhere.Append(" where A.CoTitle like '%'||@CoTitle||'%' escape '\\' and A.CoLanguage like '%'||@CoLanguage||'%' escape '\\' ");
            if (articleCategoryId != 0)
            {
                strWhere.Append(" and A.CoArticleCategoryId=@CoArticleCategoryId");
            }
            int total = DbHelperSqLite.GetSingle("select count(*) from TbArticle as A {0} ".FormatWith(strWhere)).SafeToInt();
            var parTwo = new[]
                             {
                                 new SQLiteParameter("@startIndex", DbType.Int32),
                                 new SQLiteParameter("@endIndex", DbType.Int32),
                                 new SQLiteParameter("@CoTitle", DbType.String, 255),
                                 new SQLiteParameter("@CoArticleCategoryId", DbType.Int32),
                                 new SQLiteParameter("@CoLanguage", DbType.String, 255)
                             };
            parTwo[0].Value = pageValue > 0 ? (pageValue - 1) * rowsValue : 0;
            parTwo[1].Value = rowsValue;
            parTwo[2].Value = title;
            parTwo[3].Value = articleCategoryId;
            parTwo[4].Value = language;
            var sqlTwo = new StringBuilder();
            sqlTwo.Append("select A.CoArticleId,A.CoNormalImage,A.CoSmallImage,A.CoBigImage,A.CoCoverImageId,A.CoKeyWord,A.CoDescription,A.CoTitle,A.CoArticleCategoryId,A.CoIsRecommend,A.CoIsPopular,A.CoIsTop,A.CoViews,A.CoAuthor,A.CoDate,A.CoIsReserved,A.CoReservedUrl,A.CoLanguage, B.CoName as CoCategoryName from TbArticle as A left join TbArticleCategory as B on  A.CoArticleCategoryId=B.CoArticleCategoryId ");
            sqlTwo.Append(strWhere);
            sqlTwo.AppendFormat(" order by {0} {1}  ", sort, order);
            sqlTwo.Append(" LIMIT @endIndex OFFSET @startIndex ");
            var ds = DbHelperSqLite.Query(sqlTwo.ToString(), parTwo);
            var table = new Table
                            {
                                Rows = ds.Tables["ds"].Rows,
                                Total = total
                            };
            return table;
        }

        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperSqLite.Query("select *  from TbArticle  where CoArticleId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }

        public int Add(int articleCategoryId, string title, string content, string summary, string author,
                       string language, string reservedUrl, DateTime date, bool isRecommend, bool isReserved,
                       bool isPopular, bool isTop, int[] articleImageIds, int coverId)
        {
            Check.Argument.IsNotOutOfLength(title, 2, 50, "文章标题");
            Check.Argument.IsNotOutOfLength(summary, 252, "文章简介");
            Check.Argument.IsNotOutOfLength(author, 20, "作者");
            Check.Argument.IsNotOutOfLength(language, 20, "文章语言");
            Check.Argument.IsNotOutOfLength(reservedUrl, 252, "转载地址");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("insert into TbArticle(");
            strSqlOne.Append("CoTitle,CoContent,CoSummary,CoReservedUrl,CoAuthor,CoArticleCategoryId,CoViews,CoLanguage,CoDate,CoIsReserved,CoIsPopular,CoIsTop,CoIsRecommend,CoCategoryPath,CoNormalImage,CoSmallImage,CoBigImage,CoCoverImageId");
            strSqlOne.Append(")");
            strSqlOne.Append(" values (");
            strSqlOne.Append("@CoTitle,@CoContent,@CoSummary,@CoReservedUrl,@CoAuthor,@CoArticleCategoryId,@CoViews,@CoLanguage,@CoDate,@CoIsReserved,@CoIsPopular,@CoIsTop,@CoIsRecommend,@CoCategoryPath,@CoNormalImage,@CoSmallImage,@CoBigImage,@CoCoverImageId");
            strSqlOne.Append(");");
            strSqlOne.AppendFormat("update TbArticleImage set CoArticleId=(select last_insert_rowid() from tbarticle) where CoArticleImageId in ({0}) and  exists(select * from tbarticle where coarticleid=(select last_insert_rowid() from tbarticle));",
                string.Join(",", articleImageIds.Select(o => o.ToString(CultureInfo.InvariantCulture)).ToArray()));
            strSqlOne.Append("update TbArticleImage set CoIsCover=1 where CoArticleImageId=@CoCoverImageId;");
            strSqlOne.Append("select last_insert_rowid() from TbArticle;");
            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@CoArticleCategoryId", DbType.Int32),
                    new SQLiteParameter("@CoCategoryPath", DbType.String),
                    new SQLiteParameter("@CoTitle", DbType.String, 255),
                    new SQLiteParameter("@CoContent", DbType.String),
                    new SQLiteParameter("@CoSummary", DbType.String),
                    new SQLiteParameter("@CoReservedUrl", DbType.String, 255),
                    new SQLiteParameter("@CoAuthor", DbType.String, 255),
                    new SQLiteParameter("@CoLanguage", DbType.String, 10),
                    new SQLiteParameter("@CoDate", DbType.DateTime),
                    new SQLiteParameter("@CoIsReserved", DbType.Boolean),
                    new SQLiteParameter("@CoIsPopular", DbType.Boolean),
                    new SQLiteParameter("@CoIsTop", DbType.Boolean),
                    new SQLiteParameter("@CoIsRecommend", DbType.Boolean),
                    new SQLiteParameter("@CoViews", DbType.Int32),
                    new SQLiteParameter("@CoCoverImageId", DbType.String, 255),
                    new SQLiteParameter("@CoNormalImage", DbType.String, 255),
                    new SQLiteParameter("@CoSmallImage", DbType.String, 255),
                    new SQLiteParameter("@CoBigImage", DbType.String, 255)
                };

            using (var sdr = DbHelperSqLite.ExecuteReader("select * from TbArticleCategory where CoArticleCategoryId={0}".FormatWith(articleCategoryId)))
            {
                if (sdr.Read())
                {
                    parametersOne[0].Value = articleCategoryId;
                    parametersOne[1].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    parametersOne[0].Value = DBNull.Value;
                    parametersOne[1].Value = "";
                }
            }
            parametersOne[2].Value = title;
            parametersOne[3].Value = content;
            parametersOne[4].Value = summary;
            parametersOne[5].Value = reservedUrl;
            parametersOne[6].Value = author;
            parametersOne[7].Value = language;
            parametersOne[8].Value = date;
            parametersOne[9].Value = isReserved;
            parametersOne[10].Value = isPopular;
            parametersOne[11].Value = isTop;
            parametersOne[12].Value = isRecommend;
            parametersOne[13].Value = 0;
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from TbArticleImage where CoArticleImageId={0}".FormatWith(coverId)))
            {

                if (sdr.Read())
                {
                    parametersOne[14].Value = coverId;
                    parametersOne[15].Value = sdr["CoNormalImage"].SafeToString();
                    parametersOne[16].Value = sdr["CoSmallImage"].SafeToString();
                    parametersOne[17].Value = sdr["CoBigImage"].SafeToString();
                }
                else
                {
                    parametersOne[14].Value = DBNull.Value;
                    parametersOne[15].Value = DBNull.Value;
                    parametersOne[16].Value = DBNull.Value;
                    parametersOne[17].Value = DBNull.Value;
                }
            }
            var obj = DbHelperSqLite.GetSingle(strSqlOne.ToString(), parametersOne).SafeToInt32();
            return obj;
        }

        public int Edit(int id, int articleCategoryId, string title, string content, string summary, string author,
                        string language, string reservedUrl, DateTime date, bool isRecommend, bool isReserved,
                        bool isPopular, bool isTop, int coverId)
        {
            Check.Argument.IsNotOutOfLength(title, 2, 50, "文章标题");
            Check.Argument.IsNotOutOfLength(summary, 252, "文章简介");
            Check.Argument.IsNotOutOfLength(author, 20, "作者");
            Check.Argument.IsNotOutOfLength(language, 20, "文章语言");
            Check.Argument.IsNotOutOfLength(reservedUrl, 252, "转载地址");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("update TbArticle set ");
            strSqlOne.Append("CoArticleCategoryId=@CoArticleCategoryId");
            strSqlOne.Append(",");
            strSqlOne.Append("CoCategoryPath=@CoCategoryPath");
            strSqlOne.Append(",");
            strSqlOne.Append("CoTitle=@CoTitle");
            strSqlOne.Append(",");
            strSqlOne.Append("CoContent=@CoContent");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSummary=@CoSummary");
            strSqlOne.Append(",");
            strSqlOne.Append("CoReservedUrl=@CoReservedUrl");
            strSqlOne.Append(",");
            strSqlOne.Append("CoAuthor=@CoAuthor");
            strSqlOne.Append(",");
            strSqlOne.Append("CoLanguage=@CoLanguage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoDate=@CoDate");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIsReserved=@CoIsReserved");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIsPopular=@CoIsPopular");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIsTop=@CoIsTop");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIsRecommend=@CoIsRecommend");
            strSqlOne.Append(",");
            strSqlOne.Append("CoNormalImage=@CoNormalImage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSmallImage=@CoSmallImage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoBigImage=@CoBigImage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoCoverImageId=@CoCoverImageId");
            strSqlOne.Append(" where CoArticleId=@id;");
            strSqlOne.Append("update TbArticleImage set CoIsCover=0 where CoArticleId=@id and CoIsCover=1;");
            strSqlOne.Append("update TbArticleImage set CoIsCover=1 where CoArticleImageId=@CoCoverImageId;");

            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@CoArticleCategoryId", DbType.Int32),
                    new SQLiteParameter("@CoCategoryPath", DbType.String),
                    new SQLiteParameter("@CoTitle", DbType.String, 255),
                    new SQLiteParameter("@CoContent", DbType.String),
                    new SQLiteParameter("@CoSummary", DbType.String),
                    new SQLiteParameter("@CoReservedUrl", DbType.String, 255),
                    new SQLiteParameter("@CoAuthor", DbType.String, 255),
                    new SQLiteParameter("@CoLanguage", DbType.String, 10),
                    new SQLiteParameter("@CoDate", DbType.DateTime),
                    new SQLiteParameter("@CoIsReserved", DbType.Boolean),
                    new SQLiteParameter("@CoIsPopular", DbType.Boolean),
                    new SQLiteParameter("@CoIsTop", DbType.Boolean),
                    new SQLiteParameter("@CoIsRecommend", DbType.Boolean),
                    new SQLiteParameter("@id", DbType.Int32),
                    new SQLiteParameter("@CoCoverImageId", DbType.String, 255),
                    new SQLiteParameter("@CoNormalImage", DbType.String, 255),
                    new SQLiteParameter("@CoSmallImage", DbType.String, 255),
                    new SQLiteParameter("@CoBigImage", DbType.String, 255)
                };
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from TbArticleCategory where CoArticleCategoryId={0}".FormatWith(articleCategoryId)))
            {
                if (sdr.Read())
                {
                    parametersOne[0].Value = articleCategoryId;
                    parametersOne[1].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    parametersOne[0].Value = DBNull.Value;
                    parametersOne[1].Value = "";
                }
            }
            parametersOne[2].Value = title;
            parametersOne[3].Value = content;
            parametersOne[4].Value = summary;
            parametersOne[5].Value = reservedUrl;
            parametersOne[6].Value = author;
            parametersOne[7].Value = language;
            parametersOne[8].Value = date;
            parametersOne[9].Value = isReserved;
            parametersOne[10].Value = isPopular;
            parametersOne[11].Value = isTop;
            parametersOne[12].Value = isRecommend;
            parametersOne[13].Value = id;
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from TbArticleImage where CoArticleImageId={0}".FormatWith(coverId)))
            {

                if (sdr.Read())
                {
                    parametersOne[14].Value = coverId;
                    parametersOne[15].Value = sdr["CoNormalImage"].SafeToString();
                    parametersOne[16].Value = sdr["CoSmallImage"].SafeToString();
                    parametersOne[17].Value = sdr["CoBigImage"].SafeToString();
                }
                else
                {
                    parametersOne[14].Value = DBNull.Value;
                    parametersOne[15].Value = DBNull.Value;
                    parametersOne[16].Value = DBNull.Value;
                    parametersOne[17].Value = DBNull.Value;
                }
            }
            var obj = DbHelperSqLite.ExecuteSql(strSqlOne.ToString(), parametersOne).SafeToInt();
            return obj;
        }
        public void Delete(int id)
        {
            DbHelperSqLite.ExecuteSql("delete from TbArticle where CoArticleId={0};delete from TbArticleImage where CoArticleId={0};".FormatWith(id));
        }
    }
}
